﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Web;
using Eneta.Portal.Common;


namespace Eneta.Portal.Companies
{
    public class CompanyQueryManager
    {
        private static DataTable Resulst()
        {
            var context = HttpContext.Current;
            var request = context.Request;
            if (context.Items["CompanyResults"] != null)
                return (DataTable)context.Items["CompanyResults"];
            
            var l1 = request.QueryString["l1"];
            var l2 = request.QueryString["l2"];
            var p = request.QueryString["p"];

            var county = request.QueryString["county"];
            var partner = request.QueryString["partnership"];
            var employees = request.QueryString["emp"];

            var ioRelations = WebApplicationWrapper.IoRelationsTable;
            var ioCompanyTable = WebApplicationWrapper.CompaniesIoTable;
            var ioTable = WebApplicationWrapper.IoTable;

            var ioFilter = new List<string>();
            if (!string.IsNullOrEmpty(l1))
                ioFilter.Add("Level1ID=" + l1);
            if (!string.IsNullOrEmpty(l2))
                ioFilter.Add("Level3ID=" + l2);
            if (!string.IsNullOrEmpty(p))
                ioFilter.Add("ProductID=" + p);
            
            var filterString = string.Join(" AND ", ioFilter.ToArray());
            var view = new DataView(ioTable);
            view.RowFilter = filterString;

            var results = new DataTable();

            foreach (DataColumn column in ioCompanyTable.Columns)
                results.Columns.Add(column.ColumnName, column.DataType);

            var relView = new DataView(ioRelations);
            foreach (DataRowView rowView in view)
            {
                relView.RowFilter = "IOTopicID=" + rowView["Level3ID"];
                foreach (DataRowView relRow in relView)
                {
                    var comRows = results.Select("ID=" + relRow["CompanyID"]);
                    if (comRows != null)
                        if (comRows.Length > 0)
                            continue;

                    comRows = ioCompanyTable.Select("ID=" + relRow["CompanyID"]);
                    if (comRows != null)
                        if (comRows.Length == 0)
                            continue;

                    Logger.LogInfo("ComRows content:", "WebApplicationWrapper");
                    foreach (DataColumn column in ioCompanyTable.Columns)
                        Logger.LogInfo(column.ColumnName + ": " + comRows[0][column.ColumnName], "WebApplicationWrapper");

                    if (!string.IsNullOrEmpty(partner))
                        if (comRows[0]["PartnershipLevel"].ToString() != partner)
                            continue;

                    if (!string.IsNullOrEmpty(county))
                        if (!comRows[0]["Kontorid"].ToString().Contains(county))
                            continue;

                    if (!string.IsNullOrEmpty(employees))
                        if (comRows[0]["Employees"].ToString() != employees)
                            continue;

                    var compRow = results.NewRow();
                    foreach (DataColumn column in ioCompanyTable.Columns)
                        compRow[column.ColumnName] = comRows[0][column.ColumnName];
                    results.Rows.Add(compRow);                    
                }
            }
            Logger.LogInfo("Results(): count: " + results.Rows.Count, "CompanyQueryManager");
            return results;
        }
        public static DataTable GetSelectedLevel3()
        {
            var l2 = HttpContext.Current.Request.QueryString["l2"];
            if (string.IsNullOrEmpty(l2))
                return null;

            var rows = WebApplicationWrapper.IoTable.Select("Level3ID=" + l2);
            if(rows == null)
                return null;
            if (rows.Length == 0)
                return null;

            var table = new DataTable();
            table.Columns.Add("Level3ID", typeof(int));
            table.Columns.Add("Level3Title", typeof(string));

            var row = table.NewRow();
            row["Level3ID"] = (int)rows[0]["Level3ID"];
            row["Level3Title"] = rows[0]["Level3Title"];
            table.Rows.Add(row);

            return table;
        }
        public static DataTable GetSelectedLevel1()
        {
            var l1 = HttpContext.Current.Request.QueryString["l1"];
            var l2 = HttpContext.Current.Request.QueryString["l2"];

            if (string.IsNullOrEmpty(l2) && string.IsNullOrEmpty(l1))
                return null;

            DataRow[] rows;
            if(!string.IsNullOrEmpty(l2))
                rows = WebApplicationWrapper.IoTable.Select("Level3ID=" + l2);
            else
                rows = WebApplicationWrapper.IoTable.Select("Level1ID=" + l1);
            if (rows == null)
                return null;
            if (rows.Length == 0)
                return null;

            var table = new DataTable();
            table.Columns.Add("Level1ID", typeof(int));
            table.Columns.Add("Level1Title", typeof(string));

            var row = table.NewRow();
            row["Level1ID"] = (int)rows[0]["Level1ID"];
            row["Level1Title"] = rows[0]["Level1Title"];
            table.Rows.Add(row);

            return table;
        }
        public static string GetSelectedLevel3Title()
        {
            var table = GetSelectedLevel3();
            if (table == null)
                return null;
            if (table.Rows.Count == 0)
                return null;
            return table.Rows[0]["Level3Title"].ToString();
        }
        public static string GetSelectedLevel1Title()
        {
            var table = GetSelectedLevel1();
            if (table == null)
                return null;
            if (table.Rows.Count == 0)
                return null;
            return table.Rows[0]["Level1Title"].ToString();
        }
        public static List<string> GetCounties()
        {
            Logger.LogInfo("GetCounties", "WebApplicationWrapper");
            //var resultsTable = WebApplicationWrapper.ResultsFilteredTable;
            var resultsTable = Resulst();
            var companiesTable = WebApplicationWrapper.CompaniesIoTable;
            var counties = new List<string>();
            
            //var companies = from p in companiesTable.AsEnumerable()
            //                where resultsTable.AsEnumerable().Count(s => (int)s["CompanyID"] == (int)p["ID"]) > 0
            //                select p;
            var companies = from p in companiesTable.AsEnumerable()
                            where resultsTable.AsEnumerable().Count(s => (int)s["ID"] == (int)p["ID"]) > 0
                            select p;
            Logger.LogInfo("GetCounties(): Companies found: " + companies.Count(), "WebApplicationWrapper");

            foreach (DataRow x in companies)
            {
                
                var countyRaw = x["Kontorid"].ToString();
                Logger.LogInfo("GetCounties(): Raw:" + countyRaw, "WebApplicationWrapper");
                if (string.IsNullOrEmpty(countyRaw))
                    continue;

                try
                {
                    var countyValues = countyRaw.Split(";#".ToCharArray());
                    foreach (var county in countyValues)
                    {
                        if (string.IsNullOrEmpty(county))
                            continue;
                        int countyId;
                        if (int.TryParse(county, out countyId))
                            continue;
                        if (counties.Contains(county))
                            continue;
                        counties.Add(county);
                    }
                }
                catch (Exception ex)
                {
                    Logger.LogException(ex);
                }
            }
            Logger.LogInfo("GetCounties(): Counties retrieved: " + counties.Count, "CompanyQueryManager");
            counties.Sort();
            return counties;
        }
        public static string[] GetPartnershipLevels()
        {
            //var resultsTable = WebApplicationWrapper.ResultsFilteredTable;
            var resultsTable = Resulst();
            var companiesTable = WebApplicationWrapper.CompaniesIoTable;
            var levels = new List<string>();

            //var companies = from p in companiesTable.AsEnumerable()
            //                where resultsTable.AsEnumerable().Count(s => (int)s["CompanyID"] == (int)p["ID"]) > 0
            //                orderby p["PartnershipLevel"] as string
            //                select p;
            var companies = from p in companiesTable.AsEnumerable()
                            where resultsTable.AsEnumerable().Count(s => (int)s["ID"] == (int)p["ID"]) > 0
                            orderby p["PartnershipLevel"] as string
                            select p;

            foreach (DataRow x in companies)
            {
                var level = x["PartnershipLevel"] as string;
                if (string.IsNullOrEmpty(level))
                    continue;
                if (levels.Contains(level))
                    continue;
                levels.Add(level);
            }
            var array = levels.ToArray();
            //Array.Sort(array);
            return array;
        }

        public static List<string> GetEmployees()
        {
            var resultsTable = WebApplicationWrapper.ResultsFilteredTable;
            var companiesTable = WebApplicationWrapper.CompaniesIoTable;
            var employees = new List<string>();

            var companies = from p in companiesTable.AsEnumerable()
                            where resultsTable.AsEnumerable().Count(s => (int)s["CompanyID"] == (int)p["ID"]) > 0
                            orderby p["Employees"] as string 
                            select p;

            foreach (DataRow x in companies)
            {
                var option = x["Employees"] as string;
                if (string.IsNullOrEmpty(option))
                    continue;
                if (employees.Contains(option))
                    continue;
                employees.Add(option);
            }
            return employees;
        }

        public static List<string> GetCertifiedEmployees()
        {
            var resultsTable = WebApplicationWrapper.ResultsFilteredTable;
            var companiesTable = WebApplicationWrapper.CompaniesIoTable;
            var certified = new List<string>();

            var companies = from p in companiesTable.AsEnumerable()
                            where resultsTable.AsEnumerable().Count(s => (int)s["CompanyID"] == (int)p["ID"]) > 0
                            //orderby p["CertifiedEmployees"] as int
                            select p;

            foreach (DataRow x in companies)
            {
                var option = x["CertifiedEmployees"] as string;
                if (string.IsNullOrEmpty(option))
                    continue;
                if (certified.Contains(option))
                    continue;
                certified.Add(option);
            }
            return certified;
        }

        public static DataTable GetIoClassify()
        {
            var resultsTable = WebApplicationWrapper.ResultsAllTable;
            resultsTable.DefaultView.Sort = "Level1Title, Level2Title";

            var table = new DataTable();
            table.Columns.Add("Level1ID", typeof(int));
            table.Columns.Add("Level1Title", typeof(string));
            table.Columns.Add("Level2ID", typeof(int));
            table.Columns.Add("Level2Title", typeof(string));
            table.Columns.Add("Level3ID", typeof(int));
            table.Columns.Add("Level3Title", typeof(string));

            var lastId = string.Empty;

            foreach (DataRow resultsRow in resultsTable.Rows)
            {
                if (resultsRow["Level3ID"].ToString() == lastId)
                    continue;

                lastId = resultsRow["Level3ID"].ToString();

                var row = table.NewRow();
                row["Level1ID"] = resultsRow["Level1ID"];
                row["Level1Title"] = resultsRow["Level1Title"];
                row["Level2ID"] = resultsRow["Level2ID"];
                row["Level2Title"] = resultsRow["Level2Title"];
                row["Level3ID"] = resultsRow["Level3ID"];
                row["Level3Title"] = resultsRow["Level3Title"];
                table.Rows.Add(row);
            }

            return table;
        }

        public static DataTable GetAllProducts()
        {
            var resultsTable = WebApplicationWrapper.ResultsAllTable;

            resultsTable.DefaultView.Sort = "ProductTitle";

            var productTable = new DataTable();
            productTable.Columns.Add("ProductID", typeof(int));
            productTable.Columns.Add("ProductTitle", typeof(string));

            foreach (DataRowView row in resultsTable.DefaultView)
            {
                if (row["ProductID"].ToString() == "0")
                    continue;
                var rows = productTable.Select("ProductID=" + row["ProductID"]);
                Logger.LogInfo("GetAllProducts(): Rows == null: " + (rows == null), "CompanyQueryManager");
                if (rows != null)
                    if (rows.Count() > 0)
                        continue;
                var productRow = productTable.NewRow();

                productRow["ProductID"] = row["ProductID"];
                productRow["ProductTitle"] = row["ProductTitle"];

                productTable.Rows.Add(productRow);
            }
            return productTable;
        }

        /// <summary>
        /// Returns list of products available for current request.
        /// </summary>
        /// <returns>Returns data table with products.</returns>
        public static DataTable GetProducts()
        {
            var resultsTable = WebApplicationWrapper.ResultsAllTable;
            Utility.LogDataTable(resultsTable);
            var context = HttpContext.Current;
            if (context != null)
            {
                var request = context.Request;
                var l1 = request.QueryString["l1"];
                var l2 = request.QueryString["l2"];

                if (!string.IsNullOrEmpty(l1))
                    resultsTable.DefaultView.RowFilter = "Level1ID=" + l1;
                else if (!string.IsNullOrEmpty(l2))
                    resultsTable.DefaultView.RowFilter = "Level3ID=" + l2;
            }
            Logger.LogInfo("GetProducts() RowFilter: " + resultsTable.DefaultView.RowFilter, "CompanyQueryManager");
            Logger.LogInfo("GetProducts() ViewCount: " + resultsTable.DefaultView.Count, "CompanyQueryManager");
            Logger.LogInfo("GetProducts() TableCount: " + resultsTable.Rows.Count, "CompanyQueryManager");

            resultsTable.DefaultView.Sort = "ProductTitle";

            var productTable = new DataTable();
            productTable.Columns.Add("ProductID", typeof(int));
            productTable.Columns.Add("ProductTitle", typeof(string));            

            foreach (DataRowView row in resultsTable.DefaultView)
            {
                if (row["ProductID"].ToString() == "0")
                {
                    Logger.LogInfo("GetProducts(): ProductID is 0","CompanyQueryManager");
                    continue;
                }
                var rows = productTable.Select("ProductID=" + row["ProductID"]);
                Logger.LogInfo("GetProducts(): Rows == null: " + (rows == null), "CompanyQueryManager");
                if(rows != null)
                    if(rows.Count() > 0)
                        continue;
                var productRow = productTable.NewRow();

                productRow["ProductID"] = row["ProductID"];
                productRow["ProductTitle"] = row["ProductTitle"];

                productTable.Rows.Add(productRow);
            }
            Logger.LogInfo("GetProducts() found products: " + productTable.Rows.Count, "CompanyQueryManager");
            return productTable;
        }

        internal static CompanyQueryDto GetDtoFromRequest(HttpRequest request)
        {
            var dto = new CompanyQueryDto();

            if (request == null)
                return dto;

            dto.Level1ID = GetIdFromString(request["l1"]);
            dto.Level2ID = GetIdFromString(request["l2"]);
            dto.ProductID = GetIdFromString(request["l3"]);
            dto.CountyID = GetIdFromString(request["county"]);

            return dto;
        }

        private static int GetIdFromString(string idString)
        {
            int id;

            if (!int.TryParse(idString, out id))
                return id;

            if (id < 0)
                id = 0;

            return id;
        }

        internal static InternalQueryData GetQueryResults(DataTable ioTable, DataTable ioCompanyTable, DataTable ioRelations, CompanyQueryDto dto)
        {
            Logger.LogInfo("GetQueryResults(): Start", "CompanyQueryManager");
            var result = new InternalQueryData();

            Logger.LogInfo("GetQueryResults(): Creating all results table", "CompanyQueryManager");
            var existingTable = new DataTable();
            foreach (DataColumn col in ioTable.Columns)
            {
                Logger.LogInfo("GetQueryResults(): Adding column " + col.ColumnName, "CompanyQueryManager");
                existingTable.Columns.Add(col.ColumnName, col.DataType);
            }
            Logger.LogInfo("GetQueryResults(): Executing existing info query", "CompanyQueryManager");
            var existingIo = from i in ioRelations.AsEnumerable()
                             where ioTable.AsEnumerable().Count(p => (int)p["Level3ID"] == (int)i["IOTopicID"]) > 0 &&
                                    ioCompanyTable.AsEnumerable().Count(s => (int)s["ID"] == (int)i["CompanyID"] && s["_ModerationStatus"].ToString()=="0") > 0
                             select i;

            Logger.LogInfo("GetQueryResults(): Existing info query completed", "CompanyQueryManager");
            Logger.LogInfo("GetQueryResults(): Copying results: " + existingIo.Count(), "CompanyQueryManager");

            foreach (DataRow row in existingIo)
            {
                var existingQuery = "Level3ID=" + row["IOTopicID"];
                var existingRows = existingTable.Select(existingQuery);

                if (existingRows.Count() == 0)
                {
                    var ioSelectedRows = ioTable.Select(existingQuery);
                    foreach (var selectedIoRow in ioSelectedRows)
                    {
                        var newIoRow = existingTable.NewRow();
                        foreach (DataColumn col in ioTable.Columns)
                            newIoRow[col.ColumnName] = selectedIoRow[col.ColumnName];
                        existingTable.Rows.Add(newIoRow);
                    }
                }
            }

            Logger.LogInfo("GetQueryResults(): Results copied, assigning to return value", "CompanyQueryManager");
            result.AllResults = existingTable;

            Logger.LogInfo("GetQueryResults(): Getting filtered IO results", "CompanyQueryManager");
            var ioRows = GetRequestIoResults(ioTable, dto);
            Logger.LogInfo("GetQueryResults(): Getting filtered compant results","CompanyQueryManager");
            var ioCompanies = GetRequestCompanyResults(ioCompanyTable, dto);

            Logger.LogInfo("GetQueryResults(): Executing filtered results query","CompanyQueryManager");
            var x = from i in ioRelations.AsEnumerable()
                    where ioRows.Count(p => (int)p["Level3ID"] == (int)i["IOTopicID"]) > 0 &&
                            ioCompanies.Count(s => (int)s["ID"] == (int)i["CompanyID"] && s["_ModerationStatus"].ToString() == "0") > 0
                    select i;
            Logger.LogInfo("GetQueryResults(): Filtered results: " + x.Count(), "CompanyQueryManager");


            var filteredTable = new DataTable();
            foreach (DataColumn col in ioRelations.Columns)
            {
                filteredTable.Columns.Add(col.ColumnName, col.DataType);
            }
            foreach (DataRow row in x)
            {
                var newRow = filteredTable.NewRow();

                foreach (DataColumn col in ioRelations.Columns)
                    newRow[col.ColumnName] = row[col.ColumnName];
                
                filteredTable.Rows.Add(newRow);
            }
            //foreach (DataColumn col in filteredTable.Columns)
            //    Logger.LogInfo("COL NAME:: " + col.ColumnName,"CompanyQueryManager");
            Logger.LogInfo("GetQueryResults(): Filtering results retrieved","CompanyQueryManager");
            result.FilteredResults = filteredTable;
            
            return result;
        }

        private static DataRow[] GetRequestIoResults(DataTable ioTable, CompanyQueryDto dto)
        {
            var queryParts = new List<string>();

            if (dto.Level1ID > 0)
            {
                queryParts.Add("Level1ID = " + dto.Level1ID);
            }

            if (dto.Level2ID > 0)
            {
                queryParts.Add("Level3ID = " + dto.Level2ID);
            }

            if (dto.ProductID > 0)
            {
                queryParts.Add("ProductID = " + dto.ProductID);
            }

            var query = string.Join(" And ", queryParts.ToArray());
            Debug.WriteLine("Query: " + query);

            return ioTable.Select(query);
        }

        private static DataRow[] GetRequestCompanyResults(DataTable ioCompanyTable, CompanyQueryDto dto)
        {
            var queryParts = new List<string>();


            if (dto.CountyID > 0)
            {
                queryParts.Add("County = " + dto.Level1ID);
            }

            var query = string.Join(" And ", queryParts.ToArray());
            Debug.WriteLine("Query: " + query);

            return ioCompanyTable.Select(query);
        }
    }
}
